<template>
  <div id="x-spreadsheet-box"
       :style="{width:`${spreadsheetWidth}`,height:`${spreadsheetHeight}`}"></div>
</template>

<script>
import Spreadsheet from 'x-data-spreadsheet';
import zhCN from 'x-data-spreadsheet/src/locale/zh-cn';
import XLSX from 'xlsx';
// 设置中文
Spreadsheet.locale('zh-cn', zhCN);
export default {
  name: 'WebSpreadsheet',
  props: {
    spreadsheetWidth: {
      type: String,
      default: '100%',
    },
    spreadsheetHeight: {
      type: String,
      default: '600px',
    },
    opMode: {
      type: String,
      default: 'read',
    },
    showToolbar:{
      type: Boolean,
      default: false,
    }
  },
  data() {
    return {
      xs: null,
      spreadsheetOptions: {
        mode: 'read', // edit | read
        showToolbar: false, // 工具栏显示
        showGrid: true, // 网格显示
        showContextmenu: false, // 右健菜单
        view: {
          width: () => document.getElementById('x-spreadsheet-box').offsetWidth, // 宽度
          height: () => document.getElementById('x-spreadsheet-box').offsetHeight, // 高度
        },
        row: { // 行
          len: 500,
          height: 25,
        },
        col: { // 列
          len: 60,
          width: 120,
          indexWidth: 60,
          minWidth: 60,
        },
        style: {
          bgcolor: '#ffffff',
          align: 'center',
          valign: 'middle',
          textwrap: false,
          strike: false,
          underline: false,
          color: '#000000',
          font: {
            name: 'Helvetica',
            size: 12,
            bold: false,
            italic: false,
          },
        },
      }
    };
  },
  mounted() {
    this.$nextTick(() => {
      this.init();
    })
  },
  methods: {
    init() {
      if (this.xs !== null) return;
      let { opMode } = this; 
      let { showToolbar } = this;
      this.spreadsheetOptions.mode = opMode;
      this.spreadsheetOptions.showToolbar = showToolbar;
      let spreadsheetBox = document.getElementById('x-spreadsheet-box');
      this.xs = new Spreadsheet(spreadsheetBox, this.spreadsheetOptions)
        .loadData([]);  
         

      // 派发组件已加载完成事件
      this.$nextTick(() => {
        this.$emit('spreadsheetReady')
      })
    },
    /**
     *导出excel
     */
    exportExcel() {
      var new_wb = this.xtos(this.xs.getData(), true, true);
      /* generate download */
      XLSX.writeFile(new_wb, "SheetJS.xlsx");
    },
    /**
     *打印excel Json数据
     */
    exportJson() {
      console.log(this.xs.getData())
    },
    /**
     * 导出数据方法
     * 调用xtos(sdata) 或 xtos(sdata, false, false)将给出与原始函数相同的结果（注意：单元格类型更新）
     * 如果keepMerges=truex-spreadsheet 实例中的所有合并单元格都将传输到电子表格文件。
     * 如果keepFormulas=truex 电子表格实例中的所有公式都将按原样写入其单元格中
     */
    xtos(sdata, keepMerges, keepFormulas) {
      console.log(sdata)
      console.log(keepMerges)
      console.log(keepFormulas)
      
      let self = this;
      keepMerges = keepMerges === undefined ? false : keepMerges;
      keepFormulas = keepFormulas === undefined ? false : keepFormulas;
      
       
      var out = XLSX.utils.book_new();
      sdata.forEach(function (xws) {
        var ws = {};
        var rowobj = xws.rows;
        for (var ri = 0; ri < rowobj.len; ++ri) {
          var row = rowobj[ri];
          if (!row) continue;
 
          var minCoord, maxCoord;
          Object.keys(row.cells).forEach(function (k) {
            var idx = +k;
            if (isNaN(idx)) return;

            var lastRef = self.coordinateToReference(ri + 1, idx + 1);
            if (minCoord == undefined) {
              minCoord = {
                r: ri,
                c: idx,
              };
            } else {
              if (ri < minCoord.r) minCoord.r = ri;
              if (idx < minCoord.c) minCoord.c = idx;
            }
            if (maxCoord == undefined) {
              maxCoord = {
                r: ri,
                c: idx,
              };
            } else {
              if (ri > maxCoord.r) maxCoord.r = ri;
              if (idx > maxCoord.c) maxCoord.c = idx;
            }

            var cellText = row.cells[k].text;
            var type = "s";
            if (!cellText) {
              cellText = "";
              type = "z";
            } else if (!isNaN(parseFloat(cellText))) {
              cellText = parseFloat(cellText);
              type = "n";
            } else if (cellText === "true" || cellText === "false") {
              cellText = Boolean(cellText);
              type = "b";
            }

            ws[lastRef] = {
              v: cellText,
              t: type,
            };

            if (keepFormulas && type == "s" && cellText[0] == "=") {
              ws[lastRef].f = cellText.slice(1);
            }

            if (keepMerges && row.cells[k].merge != undefined) {
              if (ws["!merges"] == undefined) ws["!merges"] = [];

              ws["!merges"].push({
                s: {
                  r: ri,
                  c: idx,
                },
                e: {
                  r: ri + row.cells[k].merge[0],
                  c: idx + row.cells[k].merge[1],
                },
              });
            }
          });

          ws["!ref"] =
            self.coordinateToReference(minCoord.r + 1, minCoord.c + 1) +
            ":" +
            self.coordinateToReference(maxCoord.r + 1, maxCoord.c + 1);
        }
        XLSX.utils.book_append_sheet(out, ws, xws.name);
      });
      console.log(out)
      return out;
    },
    /**
     * 导入数据方法
     * 调用stox(wb) 或 stox(wb, false, false)将给出与原始函数相同的结果。
     * 如果keepMerges=true电子表格文件中的所有合并单元格都将转移到 x-电子表格实例。
     * 如果keepFormulas=true电子表格文件中的所有公式都将按原样写入其单元格（而不是其解释值）
     */
    stox(wb, keepMerges, keepFormulas) {
      let self = this;
      keepMerges = keepMerges === undefined ? false : keepMerges;
      keepFormulas = keepFormulas === undefined ? false : keepFormulas;
      // console.log(wb)
      var out = [];
      wb.SheetNames.forEach(function (name) {
        var o = { name: name, rows: {} };
        var ws = wb.Sheets[name];
        var aoa = XLSX.utils.sheet_to_json(ws, {
          raw: false,
          header: 1,
        });

        aoa.forEach(function (r, i) {
          var cells = {};
          r.forEach(function (c, j) {
            cells[j] = { text: c };

            if (keepFormulas) {
              var cellRef = self.coordinateToReference(i + 1, j + 1);

              if (
                ws[cellRef] != undefined &&
                ws[cellRef].f != undefined
              ) {
                cells[j].text = "=" + ws[cellRef].f;
              }
            }
          });
          o.rows[i] = { cells: cells };
        });

        if (keepMerges && ws["!merges"]) {
          o.merges = [];
          ws["!merges"].forEach(function (merge, i) {
            //Needed to support merged cells with empty content
            if (o.rows[merge.s.r] == undefined) {
              o.rows[merge.s.r] = { cells: {} };
            }
            if (o.rows[merge.s.r].cells[merge.s.c] == undefined) {
              o.rows[merge.s.r].cells[merge.s.c] = {};
            }

            o.rows[merge.s.r].cells[merge.s.c].merge = [
              merge.e.r - merge.s.r,
              merge.e.c - merge.s.c,
            ];

            o.merges[i] =
              self.coordinateToReference(merge.s.r + 1, merge.s.c + 1) +
              ":" +
              self.coordinateToReference(merge.e.r + 1, merge.e.c + 1);
          });
        }

        out.push(o);
      });

      return out;
    },
    /**
     * 读取本地文件
     * @param fileSelected
     */
    getWorkbook(fileSelected) {
      let file = fileSelected.target.files[0]
      let reader = new FileReader()
      reader.onload = e => {
        let data = e.target.result;
        let fixedData = this.fixData(data);
        let workbook = XLSX.read(btoa(fixedData), { type: 'base64' });
        this.xs.loadData(this.stox(workbook, true, false));
      }
      reader.readAsArrayBuffer(file)
      // return workbook
    },
    fixData(data) {
      var o = ""; var l = 0; var w = 10240
      for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
      o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
      return o
    },
    /**
     * 将工作表列字母转换为其索引。
     * 警告：从 1 开始！A=>1, B=>2... 
     *
     * @param  {number} column
     * @param  {string}
     */
    columnToLetter(column) {
      var temp;
      var letter = "";
      letter = letter.toUpperCase();
      while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
      }
      return letter;
    },
    /**
     * 将参考（即 B2）转换为其坐标。
     * 警告：从 1 开始！A1 => {col: 1, row: 1} 
     *
     * @param  {string} row
     * @param  {string} col
     * @returns {string} a sheet like coordinate
     */
    coordinateToReference(row, col) {
      return this.columnToLetter(col) + String(row);
    },
    // 加载数据, 返回数据需要文件流格式
    loadExcelFile(res) {
      let fixedData = this.fixData(res);
      let workbook = XLSX.read(btoa(fixedData), { type: 'base64' });
      this.xs.loadData(this.stox(workbook, true, false));
      console.log(workbook)
      // this.xs.on('cell-selected', (cell, ri, ci) => {
      //       console.log('cell:', cell, ', 行坐标:', ri, ', 列坐标:', ci);
      //       this.xs.cellStyle(cell, 1)
      //   }).on('cell-edited', (text, ri, ci) => {
      //       console.log('text:', text, ', 行坐标: ', ri, ', 列坐标:', ci);
      //   }); 
      //   setTimeout(() => { 
      //       this.xs.loadData(this.stox(workbook, true, false));
      //       this.xs.cellText(1, 1, 'cell-text').reRender();
      //       console.log('cell(1, 1):', this.xs.cell(1, 1));
      //       console.log('cellStyle(1, 1):', this.xs.cellStyle(1, 1)).reRender();;
      //   }, 5000);
    },
  },
}
</script>

<style lang="scss" scoped>
#x-spreadsheet-box {
  margin: 0 auto;
}
</style>